package database

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"time"

	"navigation-service/internal/models"

	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB

// 数据库初始化函数
func InitDB() {
	var err error
	db, err = sql.Open("sqlite3", "./browsing-history.db")
	if err != nil {
		log.Fatal("无法打开数据库:", err)
	}

	// 创建浏览历史表
	createTableSQL := `
	CREATE TABLE IF NOT EXISTS browsing_history (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		url TEXT NOT NULL,
		title TEXT,
		visit_time TEXT NOT NULL,
		domain TEXT,
		path TEXT
	);`

	_, err = db.Exec(createTableSQL)
	if err != nil {
		log.Fatal("创建表失败:", err)
	}

	// 创建书签表
	createBookmarksTableSQL := `
	CREATE TABLE IF NOT EXISTS bookmarks (
		id TEXT PRIMARY KEY,
		title TEXT NOT NULL,
		url TEXT NOT NULL,
		date_added TEXT NOT NULL,
		parent_id TEXT
	);`

	_, err = db.Exec(createBookmarksTableSQL)
	if err != nil {
		log.Fatal("创建书签表失败:", err)
	}

	// 创建AI分组版本表
	createAIGroupingVersionTableSQL := `
	CREATE TABLE IF NOT EXISTS ai_grouping_versions (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		description TEXT,
		status TEXT NOT NULL DEFAULT 'pending',
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		completed_at DATETIME,
		total_urls INTEGER DEFAULT 0,
		grouped_urls INTEGER DEFAULT 0,
		config TEXT
	);`

	_, err = db.Exec(createAIGroupingVersionTableSQL)
	if err != nil {
		log.Fatal("创建AI分组版本表失败:", err)
	}

	// 创建AI分组表
	createAIGroupTableSQL := `
	CREATE TABLE IF NOT EXISTS ai_groups (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		version_id INTEGER NOT NULL,
		name TEXT NOT NULL,
		description TEXT,
		color TEXT,
		icon TEXT,
		order_index INTEGER DEFAULT 0,
		url_count INTEGER DEFAULT 0,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (version_id) REFERENCES ai_grouping_versions(id) ON DELETE CASCADE
	);`

	_, err = db.Exec(createAIGroupTableSQL)
	if err != nil {
		log.Fatal("创建AI分组表失败:", err)
	}

	// 创建URL分组分配表
	createURLGroupAssignmentTableSQL := `
	CREATE TABLE IF NOT EXISTS url_group_assignments (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		version_id INTEGER NOT NULL,
		group_id INTEGER NOT NULL,
		url TEXT NOT NULL,
		title TEXT,
		domain TEXT,
		path TEXT,
		visit_count INTEGER DEFAULT 0,
		last_visit DATETIME,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (version_id) REFERENCES ai_grouping_versions(id) ON DELETE CASCADE,
		FOREIGN KEY (group_id) REFERENCES ai_groups(id) ON DELETE CASCADE
	);`

	_, err = db.Exec(createURLGroupAssignmentTableSQL)
	if err != nil {
		log.Fatal("创建URL分组分配表失败:", err)
	}

	// 创建AI分组任务表
	createAIGroupingTaskTableSQL := `
	CREATE TABLE IF NOT EXISTS ai_grouping_tasks (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		version_id INTEGER NOT NULL,
		status TEXT NOT NULL DEFAULT 'pending',
		progress INTEGER DEFAULT 0,
		message TEXT,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		started_at DATETIME,
		completed_at DATETIME,
		FOREIGN KEY (version_id) REFERENCES ai_grouping_versions(id) ON DELETE CASCADE
	);`

	_, err = db.Exec(createAIGroupingTaskTableSQL)
	if err != nil {
		log.Fatal("创建AI分组任务表失败:", err)
	}

	// 创建索引
	createIndexesSQL := []string{
		"CREATE INDEX IF NOT EXISTS idx_ai_groups_version_id ON ai_groups(version_id);",
		"CREATE INDEX IF NOT EXISTS idx_url_group_assignments_version_id ON url_group_assignments(version_id);",
		"CREATE INDEX IF NOT EXISTS idx_url_group_assignments_group_id ON url_group_assignments(group_id);",
		"CREATE INDEX IF NOT EXISTS idx_url_group_assignments_url ON url_group_assignments(url);",
		"CREATE INDEX IF NOT EXISTS idx_ai_grouping_tasks_version_id ON ai_grouping_tasks(version_id);",
		"CREATE INDEX IF NOT EXISTS idx_ai_grouping_tasks_status ON ai_grouping_tasks(status);",
	}

	for _, indexSQL := range createIndexesSQL {
		_, err = db.Exec(indexSQL)
		if err != nil {
			log.Printf("创建索引失败: %v", err)
		}
	}

	fmt.Println("数据库初始化成功")
}

// 关闭数据库连接
func CloseDB() {
	if db != nil {
		db.Close()
	}
}

// 添加浏览历史记录到数据库
func AddBrowsingHistoryToDB(visits []models.BrowsingHistory) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("数据库事务开始失败: %v", err)
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare("INSERT INTO browsing_history (url, title, visit_time, domain, path) VALUES (?, ?, ?, ?, ?)")
	if err != nil {
		return fmt.Errorf("准备SQL语句失败: %v", err)
	}
	defer stmt.Close()

	for _, visit := range visits {
		_, err := stmt.Exec(visit.URL, visit.Title, visit.VisitTime, visit.Domain, visit.Path)
		if err != nil {
			return fmt.Errorf("插入数据失败: %v", err)
		}
	}

	err = tx.Commit()
	if err != nil {
		return fmt.Errorf("提交事务失败: %v", err)
	}

	return nil
}

// 获取浏览统计数据
func GetBrowsingStatsFromDB() (*models.BrowsingStats, error) {
	rows, err := db.Query("SELECT url, domain, visit_time FROM browsing_history")
	if err != nil {
		return nil, fmt.Errorf("查询数据库失败: %v", err)
	}
	defer rows.Close()

	// 计算统计数据
	totalVisits := 0
	domainCount := make(map[string]int)
	uniqueDomains := make(map[string]bool)
	todayVisits := 0
	today := time.Now().Format("2006-01-02")

	for rows.Next() {
		totalVisits++
		var url, domain, visitTime string
		err := rows.Scan(&url, &domain, &visitTime)
		if err != nil {
			return nil, fmt.Errorf("扫描数据失败: %v", err)
		}

		// 计算今日访问量
		if visitTime[:10] == today {
			todayVisits++
		}

		// 统计域名访问次数
		domainCount[domain]++
		uniqueDomains[domain] = true
	}

	// 检查迭代过程中是否有错误
	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("遍历数据失败: %v", err)
	}

	// 如果没有数据，返回默认值
	if totalVisits == 0 {
		return &models.BrowsingStats{
			TotalVisits:     0,
			MostVisitedSite: "",
			TodayVisits:     0,
			FavoriteDomain:  "",
			UniqueSites:     0,
		}, nil
	}

	// 计算最常访问的站点
	mostVisitedSite := ""
	maxVisits := 0
	favoriteDomain := ""
	for domain, count := range domainCount {
		if count > maxVisits {
			maxVisits = count
			mostVisitedSite = domain
		}
		// 选择第一个作为最喜爱的域名
		if favoriteDomain == "" {
			favoriteDomain = domain
		}
	}

	return &models.BrowsingStats{
		TotalVisits:     totalVisits,
		MostVisitedSite: mostVisitedSite,
		TodayVisits:     todayVisits,
		FavoriteDomain:  favoriteDomain,
		UniqueSites:     len(uniqueDomains),
	}, nil
}

// 获取浏览记录（按访问次数统计）
func GetBrowsingRecordsFromDB() ([]models.BrowsingRecord, error) {
	query := `
	SELECT 
		url, 
		title, 
		domain, 
		path,
		COUNT(*) as visit_count,
		MIN(visit_time) as first_visit,
		MAX(visit_time) as last_visit
	FROM browsing_history 
	GROUP BY url, title, domain, path
	ORDER BY visit_count DESC, last_visit DESC
	LIMIT 50`

	rows, err := db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("查询浏览记录失败: %v", err)
	}
	defer rows.Close()

	var records []models.BrowsingRecord
	for rows.Next() {
		var record models.BrowsingRecord
		var firstVisitStr, lastVisitStr string

		err := rows.Scan(
			&record.URL,
			&record.Title,
			&record.Domain,
			&record.Path,
			&record.VisitCount,
			&firstVisitStr,
			&lastVisitStr,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描浏览记录失败: %v", err)
		}

		// 解析时间
		firstVisit, err := time.Parse("2006-01-02T15:04:05Z", firstVisitStr)
		if err != nil {
			firstVisit, err = time.Parse("2006-01-02 15:04:05", firstVisitStr)
			if err != nil {
				firstVisit = time.Now()
			}
		}
		record.FirstVisit = firstVisit

		lastVisit, err := time.Parse("2006-01-02T15:04:05Z", lastVisitStr)
		if err != nil {
			lastVisit, err = time.Parse("2006-01-02 15:04:05", lastVisitStr)
			if err != nil {
				lastVisit = time.Now()
			}
		}
		record.LastVisit = lastVisit
		record.LastVisitTime = lastVisitStr

		records = append(records, record)
	}

	// 检查迭代过程中是否有错误
	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("遍历浏览记录失败: %v", err)
	}

	return records, nil
}

// 添加书签到数据库
func AddBookmarksToDB(bookmarks []models.Bookmark) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("数据库事务开始失败: %v", err)
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare("INSERT OR REPLACE INTO bookmarks (id, title, url, date_added, parent_id) VALUES (?, ?, ?, ?, ?)")
	if err != nil {
		return fmt.Errorf("准备SQL语句失败: %v", err)
	}
	defer stmt.Close()

	for _, bookmark := range bookmarks {
		_, err := stmt.Exec(bookmark.ID, bookmark.Title, bookmark.URL, bookmark.DateAdded, bookmark.ParentID)
		if err != nil {
			return fmt.Errorf("插入书签失败: %v", err)
		}
	}

	err = tx.Commit()
	if err != nil {
		return fmt.Errorf("提交事务失败: %v", err)
	}

	return nil
}

// 从数据库获取书签
func GetBookmarksFromDB() ([]models.Bookmark, error) {
	rows, err := db.Query("SELECT id, title, url, date_added, parent_id FROM bookmarks")
	if err != nil {
		return nil, fmt.Errorf("查询书签失败: %v", err)
	}
	defer rows.Close()

	var bookmarks []models.Bookmark
	for rows.Next() {
		var bookmark models.Bookmark
		err := rows.Scan(&bookmark.ID, &bookmark.Title, &bookmark.URL, &bookmark.DateAdded, &bookmark.ParentID)
		if err != nil {
			return nil, fmt.Errorf("扫描书签失败: %v", err)
		}
		bookmarks = append(bookmarks, bookmark)
	}

	// 检查迭代过程中是否有错误
	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("遍历书签失败: %v", err)
	}

	return bookmarks, nil
}

// ==================== AI分组相关CRUD操作 ====================

// CreateAIGroupingVersion 创建AI分组版本
func CreateAIGroupingVersion(version *models.AIGroupingVersion) error {
	query := `
	INSERT INTO ai_grouping_versions (name, description, status, config, total_urls, grouped_urls)
	VALUES (?, ?, ?, ?, ?, ?)`

	// 将Config转换为JSON字符串
	configJSON, err := json.Marshal(version.Config)
	if err != nil {
		return fmt.Errorf("序列化配置失败: %v", err)
	}

	result, err := db.Exec(query, version.Name, version.Description, version.Status, string(configJSON), version.TotalURLs, version.GroupedURLs)
	if err != nil {
		return fmt.Errorf("创建AI分组版本失败: %v", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("获取插入ID失败: %v", err)
	}

	version.ID = fmt.Sprintf("%d", id)
	return nil
}

// GetAIGroupingVersion 获取AI分组版本
func GetAIGroupingVersion(id string) (*models.AIGroupingVersion, error) {
	query := `
	SELECT id, name, description, status, created_at, updated_at, completed_at, total_urls, grouped_urls, config
	FROM ai_grouping_versions WHERE id = ?`

	row := db.QueryRow(query, id)

	var version models.AIGroupingVersion
	var createdAt, updatedAt, configJSON string
	var completedAt sql.NullString

	err := row.Scan(&version.ID, &version.Name, &version.Description, &version.Status,
		&createdAt, &updatedAt, &completedAt, &version.TotalURLs, &version.GroupedURLs, &configJSON)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("AI分组版本未找到")
		}
		return nil, fmt.Errorf("查询AI分组版本失败: %v", err)
	}

	// 解析时间
	version.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
	version.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)
	if completedAt.Valid && completedAt.String != "" {
		completed, _ := time.Parse("2006-01-02 15:04:05", completedAt.String)
		version.CompletedAt = &completed
	}

	// 解析配置JSON
	if configJSON != "" {
		err = json.Unmarshal([]byte(configJSON), &version.Config)
		if err != nil {
			return nil, fmt.Errorf("解析配置失败: %v", err)
		}
	}

	return &version, nil
}

// GetAllAIGroupingVersions 获取所有AI分组版本
func GetAllAIGroupingVersions() ([]models.AIGroupingVersion, error) {
	query := `
	SELECT id, name, description, status, created_at, updated_at, completed_at, total_urls, grouped_urls, config
	FROM ai_grouping_versions ORDER BY created_at DESC`

	rows, err := db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("查询AI分组版本失败: %v", err)
	}
	defer rows.Close()

	var versions []models.AIGroupingVersion
	for rows.Next() {
		var version models.AIGroupingVersion
		var createdAt, updatedAt string
		var completedAt sql.NullString
		var configJSON string

		err := rows.Scan(&version.ID, &version.Name, &version.Description, &version.Status,
			&createdAt, &updatedAt, &completedAt, &version.TotalURLs, &version.GroupedURLs, &configJSON)
		if err != nil {
			return nil, fmt.Errorf("扫描AI分组版本失败: %v", err)
		}

		// 解析时间
		version.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
		version.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)
		if completedAt.Valid && completedAt.String != "" {
			completed, _ := time.Parse("2006-01-02 15:04:05", completedAt.String)
			version.CompletedAt = &completed
		}

		// 解析配置JSON
		if configJSON != "" {
			err = json.Unmarshal([]byte(configJSON), &version.Config)
			if err != nil {
				return nil, fmt.Errorf("解析配置失败: %v", err)
			}
		}

		versions = append(versions, version)
	}

	return versions, nil
}

// UpdateAIGroupingVersion 更新AI分组版本
func UpdateAIGroupingVersion(version *models.AIGroupingVersion) error {
	query := `
	UPDATE ai_grouping_versions 
	SET name = ?, description = ?, status = ?, updated_at = CURRENT_TIMESTAMP, 
		completed_at = ?, total_urls = ?, grouped_urls = ?, config = ?
	WHERE id = ?`

	// 将Config转换为JSON字符串
	configJSON, err := json.Marshal(version.Config)
	if err != nil {
		return fmt.Errorf("序列化配置失败: %v", err)
	}

	_, err = db.Exec(query, version.Name, version.Description, version.Status,
		version.CompletedAt, version.TotalURLs, version.GroupedURLs, string(configJSON), version.ID)
	if err != nil {
		return fmt.Errorf("更新AI分组版本失败: %v", err)
	}

	return nil
}

// DeleteAIGroupingVersion 删除AI分组版本
func DeleteAIGroupingVersion(id int) error {
	query := `DELETE FROM ai_grouping_versions WHERE id = ?`

	_, err := db.Exec(query, id)
	if err != nil {
		return fmt.Errorf("删除AI分组版本失败: %v", err)
	}

	return nil
}

// CreateAIGroup 创建AI分组
func CreateAIGroup(group *models.AIGroup) error {
	query := `
	INSERT INTO ai_groups (version_id, name, description, color, icon, order_index, url_count)
	VALUES (?, ?, ?, ?, ?, ?, ?)`

	result, err := db.Exec(query, group.VersionID, group.Name, group.Description,
		group.Color, group.Icon, group.Order, group.URLCount)
	if err != nil {
		return fmt.Errorf("创建AI分组失败: %v", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("获取插入ID失败: %v", err)
	}

	group.ID = fmt.Sprintf("%d", id)
	return nil
}

// GetAIGroupsByVersionID 根据版本ID获取AI分组
func GetAIGroupsByVersionID(versionID int) ([]models.AIGroup, error) {
	query := `
	SELECT id, version_id, name, description, color, icon, order_index, url_count, created_at, updated_at
	FROM ai_groups WHERE version_id = ? ORDER BY order_index ASC`

	rows, err := db.Query(query, versionID)
	if err != nil {
		return nil, fmt.Errorf("查询AI分组失败: %v", err)
	}
	defer rows.Close()

	var groups []models.AIGroup
	for rows.Next() {
		var group models.AIGroup
		var createdAt, updatedAt string

		err := rows.Scan(&group.ID, &group.VersionID, &group.Name, &group.Description,
			&group.Color, &group.Icon, &group.Order, &group.URLCount, &createdAt, &updatedAt)
		if err != nil {
			return nil, fmt.Errorf("扫描AI分组失败: %v", err)
		}

		// 解析时间
		group.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
		group.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)

		groups = append(groups, group)
	}

	return groups, nil
}

// UpdateAIGroup 更新AI分组
func UpdateAIGroup(group *models.AIGroup) error {
	query := `
	UPDATE ai_groups 
	SET name = ?, description = ?, color = ?, icon = ?, order_index = ?, url_count = ?, updated_at = CURRENT_TIMESTAMP
	WHERE id = ?`

	_, err := db.Exec(query, group.Name, group.Description, group.Color,
		group.Icon, group.Order, group.URLCount, group.ID)
	if err != nil {
		return fmt.Errorf("更新AI分组失败: %v", err)
	}

	return nil
}

// DeleteAIGroup 删除AI分组
func DeleteAIGroup(id int) error {
	query := `DELETE FROM ai_groups WHERE id = ?`

	_, err := db.Exec(query, id)
	if err != nil {
		return fmt.Errorf("删除AI分组失败: %v", err)
	}

	return nil
}

// CreateURLGroupAssignment 创建URL分组分配
func CreateURLGroupAssignment(assignment *models.URLGroupAssignment) error {
	query := `
	INSERT INTO url_group_assignments (version_id, group_id, url, title, domain, path, visit_count, last_visit)
	VALUES (?, ?, ?, ?, ?, ?, ?, ?)`

	_, err := db.Exec(query, assignment.VersionID, assignment.GroupID, assignment.URL,
		assignment.Title, assignment.Domain, assignment.Path, assignment.VisitCount, assignment.LastVisit)
	if err != nil {
		return fmt.Errorf("创建URL分组分配失败: %v", err)
	}

	return nil
}

// GetURLGroupAssignmentsByVersionID 根据版本ID获取URL分组分配
func GetURLGroupAssignmentsByVersionID(versionID int) ([]models.URLGroupAssignment, error) {
	query := `
	SELECT id, version_id, group_id, url, title, domain, path, visit_count, last_visit, created_at, updated_at
	FROM url_group_assignments WHERE version_id = ? ORDER BY created_at DESC`

	rows, err := db.Query(query, versionID)
	if err != nil {
		return nil, fmt.Errorf("查询URL分组分配失败: %v", err)
	}
	defer rows.Close()

	var assignments []models.URLGroupAssignment
	for rows.Next() {
		var assignment models.URLGroupAssignment
		var lastVisit, createdAt, updatedAt string

		err := rows.Scan(&assignment.ID, &assignment.VersionID, &assignment.GroupID, &assignment.URL,
			&assignment.Title, &assignment.Domain, &assignment.Path, &assignment.VisitCount,
			&lastVisit, &createdAt, &updatedAt)
		if err != nil {
			return nil, fmt.Errorf("扫描URL分组分配失败: %v", err)
		}

		// 解析时间
		assignment.LastVisit, _ = time.Parse("2006-01-02 15:04:05", lastVisit)
		assignment.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
		assignment.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)

		assignments = append(assignments, assignment)
	}

	return assignments, nil
}

// GetURLGroupAssignmentsByGroupID 根据分组ID获取URL分组分配
func GetURLGroupAssignmentsByGroupID(groupID int) ([]models.URLGroupAssignment, error) {
	query := `
	SELECT id, version_id, group_id, url, title, domain, path, visit_count, last_visit, created_at, updated_at
	FROM url_group_assignments WHERE group_id = ? ORDER BY visit_count DESC, last_visit DESC`

	rows, err := db.Query(query, groupID)
	if err != nil {
		return nil, fmt.Errorf("查询URL分组分配失败: %v", err)
	}
	defer rows.Close()

	var assignments []models.URLGroupAssignment
	for rows.Next() {
		var assignment models.URLGroupAssignment
		var lastVisit, createdAt, updatedAt string

		err := rows.Scan(&assignment.ID, &assignment.VersionID, &assignment.GroupID, &assignment.URL,
			&assignment.Title, &assignment.Domain, &assignment.Path, &assignment.VisitCount,
			&lastVisit, &createdAt, &updatedAt)
		if err != nil {
			return nil, fmt.Errorf("扫描URL分组分配失败: %v", err)
		}

		// 解析时间
		assignment.LastVisit, _ = time.Parse("2006-01-02 15:04:05", lastVisit)
		assignment.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
		assignment.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)

		assignments = append(assignments, assignment)
	}

	return assignments, nil
}

// UpdateURLGroupAssignment 更新URL分组分配
func UpdateURLGroupAssignment(assignment *models.URLGroupAssignment) error {
	query := `
	UPDATE url_group_assignments 
	SET group_id = ?, title = ?, domain = ?, path = ?, visit_count = ?, last_visit = ?, updated_at = CURRENT_TIMESTAMP
	WHERE id = ?`

	_, err := db.Exec(query, assignment.GroupID, assignment.Title, assignment.Domain,
		assignment.Path, assignment.VisitCount, assignment.LastVisit, assignment.ID)
	if err != nil {
		return fmt.Errorf("更新URL分组分配失败: %v", err)
	}

	return nil
}

// DeleteURLGroupAssignment 删除URL分组分配
func DeleteURLGroupAssignment(id int) error {
	query := `DELETE FROM url_group_assignments WHERE id = ?`

	_, err := db.Exec(query, id)
	if err != nil {
		return fmt.Errorf("删除URL分组分配失败: %v", err)
	}

	return nil
}

// CreateAIGroupingTask 创建AI分组任务
func CreateAIGroupingTask(task *models.AIGroupingTask) error {
	query := `
	INSERT INTO ai_grouping_tasks (version_id, status, progress, message)
	VALUES (?, ?, ?, ?)`

	result, err := db.Exec(query, task.VersionID, task.Status, task.Progress, task.Message)
	if err != nil {
		return fmt.Errorf("创建AI分组任务失败: %v", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("获取插入ID失败: %v", err)
	}

	task.ID = fmt.Sprintf("%d", id)
	return nil
}

// GetAIGroupingTaskByVersionID 根据版本ID获取AI分组任务
func GetAIGroupingTaskByVersionID(versionID int) (*models.AIGroupingTask, error) {
	query := `
	SELECT id, version_id, status, progress, message, created_at, updated_at, started_at, completed_at
	FROM ai_grouping_tasks WHERE version_id = ? ORDER BY created_at DESC LIMIT 1`

	row := db.QueryRow(query, versionID)

	var task models.AIGroupingTask
	var createdAt, updatedAt string
	var startedAt, completedAt sql.NullString

	err := row.Scan(&task.ID, &task.VersionID, &task.Status, &task.Progress, &task.Message,
		&createdAt, &updatedAt, &startedAt, &completedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("AI分组任务未找到")
		}
		return nil, fmt.Errorf("查询AI分组任务失败: %v", err)
	}

	// 解析时间
	task.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
	task.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)
	if startedAt.Valid && startedAt.String != "" {
		started, _ := time.Parse("2006-01-02 15:04:05", startedAt.String)
		task.StartedAt = started
	}
	if completedAt.Valid && completedAt.String != "" {
		completed, _ := time.Parse("2006-01-02 15:04:05", completedAt.String)
		task.CompletedAt = &completed
	}

	return &task, nil
}

// UpdateAIGroupingTask 更新AI分组任务
func UpdateAIGroupingTask(task *models.AIGroupingTask) error {
	query := `
	UPDATE ai_grouping_tasks 
	SET status = ?, progress = ?, message = ?, updated_at = CURRENT_TIMESTAMP, 
		started_at = ?, completed_at = ?
	WHERE id = ?`

	_, err := db.Exec(query, task.Status, task.Progress, task.Message,
		task.StartedAt, task.CompletedAt, task.ID)
	if err != nil {
		return fmt.Errorf("更新AI分组任务失败: %v", err)
	}

	return nil
}

// UpdateAIGroupingVersionStatus 更新AI分组版本状态
func UpdateAIGroupingVersionStatus(versionID string, status string) error {
	query := `UPDATE ai_grouping_versions SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`

	_, err := db.Exec(query, status, versionID)
	if err != nil {
		return fmt.Errorf("更新AI分组版本状态失败: %v", err)
	}

	return nil
}

// UpdateAIGroupingVersionStats 更新AI分组版本统计信息
func UpdateAIGroupingVersionStats(versionID string, totalURLs int, groupedURLs int) error {
	query := `UPDATE ai_grouping_versions SET total_urls = ?, grouped_urls = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`

	_, err := db.Exec(query, totalURLs, groupedURLs, versionID)
	if err != nil {
		return fmt.Errorf("更新AI分组版本统计信息失败: %v", err)
	}

	return nil
}

// ClearAIGroupsByVersionID 清空指定版本的所有分组
func ClearAIGroupsByVersionID(versionID string) error {
	// 开始事务
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("开始事务失败: %v", err)
	}
	defer tx.Rollback()

	// 1. 删除URL分配记录
	_, err = tx.Exec(`DELETE FROM url_group_assignments WHERE version_id = ?`, versionID)
	if err != nil {
		return fmt.Errorf("删除URL分配记录失败: %v", err)
	}

	// 2. 删除AI分组
	_, err = tx.Exec(`DELETE FROM ai_groups WHERE version_id = ?`, versionID)
	if err != nil {
		return fmt.Errorf("删除AI分组失败: %v", err)
	}

	// 3. 重置版本统计信息
	_, err = tx.Exec(`UPDATE ai_grouping_versions SET total_urls = 0, grouped_urls = 0, status = 'created', updated_at = CURRENT_TIMESTAMP WHERE id = ?`, versionID)
	if err != nil {
		return fmt.Errorf("重置版本统计信息失败: %v", err)
	}

	// 提交事务
	if err = tx.Commit(); err != nil {
		return fmt.Errorf("提交事务失败: %v", err)
	}

	return nil
}

// ClearAllAIGroups 清空所有分组信息数据
func ClearAllAIGroups() error {
	// 开始事务
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("开始事务失败: %v", err)
	}
	defer tx.Rollback()

	// 1. 删除所有URL分配记录
	_, err = tx.Exec(`DELETE FROM url_group_assignments`)
	if err != nil {
		return fmt.Errorf("删除所有URL分配记录失败: %v", err)
	}

	// 2. 删除所有AI分组
	_, err = tx.Exec(`DELETE FROM ai_groups`)
	if err != nil {
		return fmt.Errorf("删除所有AI分组失败: %v", err)
	}

	// 3. 重置所有版本统计信息
	_, err = tx.Exec(`UPDATE ai_grouping_versions SET total_urls = 0, grouped_urls = 0, status = 'created', updated_at = CURRENT_TIMESTAMP`)
	if err != nil {
		return fmt.Errorf("重置所有版本统计信息失败: %v", err)
	}

	// 提交事务
	if err = tx.Commit(); err != nil {
		return fmt.Errorf("提交事务失败: %v", err)
	}

	return nil
}

// DeleteAllAIGroupsAndData 删除所有分组版本、分组和URL分配数据
func DeleteAllAIGroupsAndData() error {
	// 开始事务
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("开始事务失败: %v", err)
	}
	defer tx.Rollback()

	// 1. 删除所有URL分配记录
	_, err = tx.Exec(`DELETE FROM url_group_assignments`)
	if err != nil {
		return fmt.Errorf("删除所有URL分配记录失败: %v", err)
	}

	// 2. 删除所有AI分组
	_, err = tx.Exec(`DELETE FROM ai_groups`)
	if err != nil {
		return fmt.Errorf("删除所有AI分组失败: %v", err)
	}

	// 3. 删除所有AI分组任务
	_, err = tx.Exec(`DELETE FROM ai_grouping_tasks`)
	if err != nil {
		return fmt.Errorf("删除所有AI分组任务失败: %v", err)
	}

	// 4. 删除所有AI分组版本
	_, err = tx.Exec(`DELETE FROM ai_grouping_versions`)
	if err != nil {
		return fmt.Errorf("删除所有AI分组版本失败: %v", err)
	}

	// 提交事务
	if err = tx.Commit(); err != nil {
		return fmt.Errorf("提交事务失败: %v", err)
	}

	return nil
}

// GetDB 获取数据库连接
func GetDB() *sql.DB {
	return db
}
